// database/users.test.js
import { promisify } from 'util';
import node_path_1 from "node:path";
import sqlite3_1 from "sqlite3";
let usersDB;

function initUsersDatabase() {
    var dbPath = node_path_1.default.join(process.cwd(), 'users.db');
    exports.usersDB = usersDB = new sqlite3_1.default.Database(dbPath, function (err) {
        if (err) {
            console.error('Users database opening error:', err.message);
            return;
        }
        console.log('Users database connected successfully');
        usersDB.run("CREATE TABLE IF NOT EXISTS users (\n                id TEXT PRIMARY KEY,\n                username TEXT UNIQUE NOT NULL,\n                password TEXT NOT NULL,\n                email TEXT UNIQUE NOT NULL,\n                avatar TEXT,\n                role TEXT NOT NULL,\n                permissions TEXT NOT NULL,\n                createdAt DATETIME NOT NULL,\n                updatedAt DATETIME NOT NULL,\n                name TEXT,\n                phone TEXT,\n                address TEXT,\n                city TEXT,\n                code TEXT,\n                about TEXT,\n                isActive BOOLEAN DEFAULT 1,\n                lastLogin DATETIME\n            )", function (createTableErr) {
            if (createTableErr) {
                console.error('Users table creation error:', createTableErr.message);
            }
            else {
                console.log('Table "users" created/verified');
                // Insert test user if none exists
                insertTestUserIfNeeded();
            }
        });
    });
}
// 将回调式方法转为Promise
// const dbRun = (sql, params) => {
//     return new Promise((resolve, reject) => {
//         usersDB.run(sql, params, function(err) {
//             if (err) reject(err);
//             else resolve(this);
//         });
//     });
// };
//
// const dbGet = promisify(usersDB.get.bind(usersDB));
// const dbAll = promisify(usersDB.all.bind(usersDB));

async function initializeDatabase() {
    try {
        console.log('初始化用户数据库...');
        await initUsersDatabase();
        const usersDB = await getUsersDB(); // 确保获取已初始化的数据库实例

        // 将回调式方法转为Promise
        const dbRun = (sql, params) => {
            return new Promise((resolve, reject) => {
                usersDB.run(sql, params, function(err) {
                    if (err) reject(err);
                    else resolve(this);
                });
            });
        };

        const dbGet = promisify(usersDB.get.bind(usersDB));
        const dbAll = promisify(usersDB.all.bind(usersDB));

        console.log('\n测试1: 检查表结构');
        const tableInfo = await dbAll("PRAGMA table_info(users)");
        console.log('表结构:', tableInfo.map(col => `${col.name} (${col.type})`).join(', '));

        console.log('\n测试2: 查询测试用户');
        const testUser = await dbGet("SELECT * FROM users WHERE username = 'test'");
        if (testUser) {
            console.log('找到测试用户:', {
                id: testUser.id,
                username: testUser.username,
                email: testUser.email,
                role: JSON.parse(testUser.role),
                isActive: testUser.isActive
            });
        } else {
            console.log('未找到测试用户');
        }

        console.log('\n测试3: 测试read-users查询');
        try {
            const users = await dbAll(`
                SELECT 
                    id,
                    username,
                    email,
                    name,
                    phone,
                    role,
                    isActive,
                    createdAt,
                    updatedAt
                FROM users 
                ORDER BY createdAt DESC
            `);

            console.log(`找到 ${users.length} 个用户`);
            users.forEach(user => {
                console.log(`- ${user.username} (${user.email})`, {
                    role: JSON.parse(user.role),
                    isActive: user.isActive
                });
            });
        } catch (err) {
            console.error('read-users查询失败:', err);
            console.log('\n尝试使用原始SQL查询...');
            const originalSql = `
                SELECT 
                    id,
                    username,
                    email,
                    name,
                    phone,
                    role,
                    is_active AS isActive,
                    created_at AS createdAt,
                    updated_at AS updatedAt
                FROM users 
                ORDER BY created_at DESC
            `;
            try {
                await dbAll(originalSql);
                console.error('原始SQL查询成功 - 问题出在字段名不匹配');
            } catch (err2) {
                console.error('原始SQL查询也失败:', err2);
            }
        }

        console.log('\n测试4: 插入新用户');
        const newUser = {
            id: 'test-' + Date.now(),
            username: 'testuser',
            password: 'password123',
            email: 'test@example.com',
            role: JSON.stringify({ name: 'user', permission: [] }),
            permissions: JSON.stringify([]),
            createdAt: new Date().toISOString(),
            updatedAt: new Date().toISOString(),
            isActive: 1
        };

        await dbRun(
            `INSERT INTO users
             (id, username, password, email, role, permissions, createdAt, updatedAt, isActive)
             VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?)`,
            [
                newUser.id,
                newUser.username,
                newUser.password,
                newUser.email,
                newUser.role,
                newUser.permissions,
                newUser.createdAt,
                newUser.updatedAt,
                newUser.isActive
            ]
        );
        console.log('新用户插入成功');

    } catch (error) {
        console.error('测试失败:', error);
    } finally {
        if (usersDB) {
            usersDB.close();
        }
    }
}

initializeDatabase();